package com.pheruagor.handmanager.storage.impl.sqlite {
    import flash.data.SQLConnection;
    import flash.data.SQLStatement;
    import flash.errors.SQLError;
    import flash.events.SQLErrorEvent;
    import flash.events.SQLEvent;
    import flash.filesystem.File;


    public class SQLiteStorage {
        // Name used to create the sqlite file on system
        public static const DATABASE_NAME:String = "handManager.sqlite";
        //-----------------------------------------------------
        // Properties
        //-----------------------------------------------------
        protected static var m_instance:SQLiteStorage;

        private var m_sqlConnection:SQLConnection;
        private var m_stmt:SQLStatement;
        private var m_file:File;

        //-----------------------------------------------------
        // Initialization
        //-----------------------------------------------------
        public function SQLiteStorage():void {
            m_stmt = new SQLStatement();
        }

        //-----------------------------------------------------
        // Operation
        //-----------------------------------------------------
        /**
         * save data provide by sql statement in specify table.
         * result handler is call on sql result
         *
         * @param tableName
         * 			name of table to use for save
         * @param resultHandler
         * 			handler call on sql result
         * @param errorHandler
         * 			handler call on sql error
         *
         */
        public function save(table:Table, resultHandler:Function, errorHandler:Function = null):void {
            connect(table);
            m_stmt.clearParameters();
            m_stmt.sqlConnection = m_sqlConnection;
            m_stmt.text = "INSERT OR REPLACE INTO " + table.name + table.insertStatement;
            var onResult:Function = function(event:SQLEvent):void {
                    m_stmt.removeEventListener(SQLEvent.RESULT, onResult);
                    m_stmt.removeEventListener(SQLErrorEvent.ERROR, onSQLError);
                    if (resultHandler != null) {
                        resultHandler(event);
                    }
                    disconnect();
                }
            var onSQLError:Function = function(event:SQLError):void {
                    m_stmt.removeEventListener(SQLEvent.RESULT, onResult);
                    m_stmt.removeEventListener(SQLErrorEvent.ERROR, onSQLError);
                    if (errorHandler != null) {
                        errorHandler(event);
                    } else {
                        onError(event);
                    }
                    disconnect();
                }
            m_stmt.addEventListener(SQLEvent.RESULT, onResult);
            m_stmt.addEventListener(SQLErrorEvent.ERROR, onSQLError);
            m_stmt.execute();
        }

        private function onError(event:SQLError):void {
            m_stmt.removeEventListener(SQLErrorEvent.ERROR, onError);
            trace('Error: [' + event.getStackTrace() + "]");
            disconnect();
        }

        /**
         * remove entry provide by sql statement in specify table.
         * result handler is call on sql result
         *
         * @param tableName
         * 			name of table to remove
         * @param resultHandler
         * 			handler call on sql result
         * @param errorHandler
         * 			handler call on sql error
         *
         */
        public function remove(table:Table, resultHandler:Function, errorHandler:Function = null):void {
            connect(table);
            m_stmt.clearParameters();
            m_stmt.sqlConnection = m_sqlConnection;
            m_stmt.text = "DELETE FROM " + table.name + " " + table.removeStatement;
            var onResult:Function = function(event:SQLEvent):void {
                    m_stmt.removeEventListener(SQLEvent.RESULT, onResult);
                    m_stmt.removeEventListener(SQLErrorEvent.ERROR, onSQLError);
                    if (resultHandler != null) {
                        resultHandler(event);
                    }
                    disconnect();
                }
            var onSQLError:Function = function(event:SQLError):void {
                    m_stmt.removeEventListener(SQLEvent.RESULT, onResult);
                    m_stmt.removeEventListener(SQLErrorEvent.ERROR, onSQLError);
                    if (errorHandler != null) {
                        errorHandler(event);
                    } else {
                        onError(event);
                    }
                    disconnect();
                }
            m_stmt.addEventListener(SQLEvent.RESULT, onResult);
            m_stmt.addEventListener(SQLErrorEvent.ERROR, onSQLError);
            m_stmt.execute();
        }

        /**
         * load entry with id provide by sql statement in specify table.
         * result handler is call on sql result
         *
         * @param tableName
         * 			name of table to remove
         * @param resultHandler
         * 			handler call on sql result
         * @param errorHandler
         * 			handler call on sql error
         *
         */
        public function load(table:Table, resultHandler:Function, errorHandler:Function = null):void {
            disconnect();
            connect(table);
            m_stmt.clearParameters();
            m_stmt.sqlConnection = m_sqlConnection;
            m_stmt.text = "SELECT " + table.selectStatement;
            var onResult:Function = function(event:SQLEvent):void {
                    m_stmt.removeEventListener(SQLEvent.RESULT, onResult);
                    m_stmt.removeEventListener(SQLErrorEvent.ERROR, onSQLError);
                    if (resultHandler != null) {
                        resultHandler(event);
                    }
                    disconnect();
                }
            var onSQLError:Function = function(event:SQLError):void {
                    m_stmt.removeEventListener(SQLEvent.RESULT, onResult);
                    m_stmt.removeEventListener(SQLErrorEvent.ERROR, onSQLError);
                    if (errorHandler != null) {
                        errorHandler(event);
                    } else {
                        onError(event);
                    }
                    disconnect();
                }
            m_stmt.addEventListener(SQLErrorEvent.ERROR, onSQLError);
            m_stmt.addEventListener(SQLEvent.RESULT, onResult);
            m_stmt.execute();
        }

        //-----------------------------------------------------
        // Implementation
        //-----------------------------------------------------
        private function connect(table:Table):void {
            m_file = File.applicationStorageDirectory.resolvePath(DATABASE_NAME);
            var fileExists:Boolean = m_file.exists;
            m_sqlConnection = new SQLConnection();
            m_sqlConnection.open(m_file);
            createTable(table);
        }

        private function disconnect():void {
            if (m_sqlConnection != null && m_sqlConnection.connected) {
                m_sqlConnection.close();
            }
        }

        private function createTable(table:Table):void {
            var stmt:SQLStatement = new SQLStatement();
            stmt.sqlConnection = m_sqlConnection;
            stmt.text = "CREATE TABLE IF NOT EXISTS " + table.name + table.creationStatement;
            stmt.execute();
            stmt.clearParameters();
        }

    }
}